
from App.DBUtil import DBUtil

Mapper = DBUtil()
class FlyInfoDAO():

    def get_all(self):
        sql = 'SELECT * from fly_info'
        datas = Mapper.exceptSql(sql)
        return datas

    def insert(self, data):
        sql = 'INSERT INTO `fly_info`.`wbdflightlist`(`airCode`, `arrAirport`, `arrAirportCode`, `arrDate`, `arrTerminal`, `arrTime`, `cabin`, `date`, `depAirport`, `depAirportCode`, `depDate`, `depTerminal`, `depTime`, `distance`, `flightTime`, `fullName`, `mainCarrierFullName`, `mealDesc`, `name`, `planeFullType`, `planeType`, `shortCarrier`, `shortName`, `minPrice`, `code`, `flightType`, `discountStr`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
        Mapper.executeSql(sql, data)

    def get_ArrivalAirport_nums(self):
        sql = 'SELECT searchArrivalAirport, COUNT(searchArrivalAirport) AS cntNum FROM wbdflightlist GROUP BY searchArrivalAirport ORDER BY cntNum DESC LIMIT 30'
        datas = Mapper.exceptSql(sql)
        return datas

    def insertAirpot(self, data):
        sql = 'INSERT INTO `fly_info`.`airport`(`city`, `name`, `pinyin`, `code`, `distance`, `province`) VALUES (%s, %s, %s, %s, %s, %s);'
        Mapper.executeSql(sql, data)

    def queryCityAirportSort(self):
        sql = 'SELECT COUNT(arrAirport) AS c, arrAirport, searchArrivalAirport FROM wbdflightlist GROUP BY arrAirport, searchArrivalAirport ORDER BY c DESC LIMIT 30'
        datas = Mapper.exceptSql(sql)
        return datas

    def queryFullNameSort(self):
        sql = 'SELECT fullName, COUNT(fullName) AS c FROM wbdflightlist GROUP BY fullName ORDER BY c DESC LIMIT 30'
        datas = Mapper.exceptSql(sql)
        return datas

    def queryPrice(self):
        sql = 'SELECT minPrice FROM wbdflightlist ORDER BY minPrice'
        datas = Mapper.exceptSql(sql)
        return datas
    
    def queryTotalPrices(self):
        sql = 'SELECT fullName, SUM(minPrice) AS totalPrice FROM wbdflightlist GROUP BY fullName ORDER BY totalPrice DESC'
        datas = Mapper.exceptSql(sql)
        return datas

    def queryPCs(sql):
        # SELECT id, arrTime, score FROM wbdflightlist	WHERE searchArrivalAirport = '深圳' AND searchDepartureAirport = '上海'
        sql = "SELECT arrTime, depTime, distance, flightTime, mealDesc, minPrice, score FROM wbdflightlist WHERE searchArrivalAirport = '深圳' AND searchDepartureAirport = '上海'"
        datas = Mapper.exceptSql(sql)
        return datas